leetcode-sql/medium/585. Investments in 2016.sql at main · cM2908/leetcode-sql · GitHub

Name: leetcode-sql/medium/585. Investments in 2016.sql at main · cM2908/leetcode-sql · GitHub
Rating: 4.9 (2771 reviews)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
--(1st approach is called co-relevant subquery because inner query is dependent on outer query)

SELECTSUM(tiv_2016) AS tiv_2016_sum
FROM insurance_585 i
WHERE tiv_2015 IN (SELECT tiv_2015
FROM insurance_585
WHERE pid <>i.pid) AND
 (lat,lon) NOT IN (SELECT lat,lon
FROM insurance_585
WHERE pid <>i.pid);


(OR)


SELECTSUM(tiv_2016) AS tiv_2016_sum
FROM insurance_585
WHERE tiv_2015 IN (SELECT tiv_2015
FROM insurance_585
GROUP BY tiv_2015
HAVINGCOUNT(*) >1) AND
(lat,lon) IN (SELECT lat,lon
FROM insurance_585
GROUP BY lat,lon
HAVINGCOUNT(*) =1);